﻿Imports System.Data.SqlClient

Public Class frmCashFlow

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Try
            Me.Cursor = Cursors.WaitCursor
            Dim Acc, SubAcc, Year As String
            'Dim M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12 As Double

            Dim das As New DataSet
            Dim dap As New SqlDataAdapter("Select N'" & Me.DTPPeriodFRm.Value.ToString & "' DestSource,N'" & _
                                                 Me.DTPPeriodTo.Value.ToString & "' CustName," & _
                                                 GetCashBalance(Me.DTPPeriodFRm.Value.ToShortDateString & " 00:0:01") & _
                                                 " MoveNo,N'Receipts' Acc3,Acc1,Acc2,Sum(TotalIn) TotalIn From Transactions Where MoveNo In " & _
                                                 "(Select MoveNo From Transactions Where TransType=N'Receipt' and " & _
                                                 " TransDate>=N'" & Me.DTPPeriodFRm.Value.ToShortDateString & " 00:00:00' " & _
                                                 " and TransDate<=N'" & Me.DTPPeriodTo.Value.ToShortDateString & " 23:59:59') " & _
                                                 "Group By Acc1,Acc2 Having Sum(TotalIn)<>0", cnn)
            dap.Fill(das, "Transactions")

            Dim dap1 As New SqlDataAdapter("Select N'" & Me.DTPPeriodFRm.Value.ToString & "' DestSource,N'" & _
                                                 Me.DTPPeriodTo.Value.ToString & "' CustName," & _
                                                 GetCashBalance(Me.DTPPeriodFRm.Value.ToShortDateString & " 00:0:01") & _
                                                 " MoveNo,N'Disbursements' Acc3,Acc1,Acc2,-1 * Sum(TotalOut) TotalIn From Transactions Where MoveNo In " & _
                                                 "(Select MoveNo From Transactions Where TransType=N'Pay' and " & _
                                                 " TransDate>=N'" & Me.DTPPeriodFRm.Value.ToShortDateString & " 00:00:00' " & _
                                                 " and TransDate<=N'" & Me.DTPPeriodTo.Value.ToShortDateString & " 23:59:59') " & _
                                                 "Group By Acc1,Acc2 Having Sum(TotalOut)<>0", cnn)
            dap1.Fill(das, "Transactions")

            Dim rpt As New CashFlow
            rpt.SetDataSource(das)
            rptViewer.CrystalReportViewer1.ReportSource = rpt
            rptViewer.CrystalReportViewer1.RefreshReport()
            rptViewer.ShowDialog()
            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Function GetCashBalance(ByVal Day As String) As Double
        Try
            Dim Cash, Bank, Amount As Double
            Dim cmd As New SqlCommand("Select IsNull(Sum(TotalOut)-Sum(TotalIn),0) From Transactions Where " & _
                                      "Acc1=N'Current Assets' and Acc2=N'Cash' and TransDate<N'" & Day & "'", cnn4)
            Dim cmd1 As New SqlCommand("Select IsNull(Sum(TotalOut)-Sum(TotalIn),0) From Transactions Where " & _
                                      "Acc1=N'Current Assets' and Acc2=N'Bank Accounts' and TransDate<N'" & Day & "'", cnn4)

            cnn4.Open()
            Cash = CDbl(cmd.ExecuteScalar.ToString)
            Bank = CDbl(cmd1.ExecuteScalar.ToString)
            cnn4.Close()

            Amount = Cash + Bank

            Return Amount
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    'Year = Me.CombYear.SelectedItem

    '1/ Get Open Balance
    'Dim rowOpenBalance As String() = {Year, Acc, SubAcc, GetCashBalance("1/1/" & Year & " 00:0:01"), _
    '                                                GetCashBalance("2/1/" & Year & " 00:0:01"), _
    '                                                GetCashBalance("3/1/" & Year & " 00:0:01"), _
    '                                                GetCashBalance("4/1/" & Year & " 00:0:01"), _
    '                                                GetCashBalance("5/1/" & Year & " 00:0:01"), _
    '                                                GetCashBalance("6/1/" & Year & " 00:0:01"), _
    '                                                GetCashBalance("7/1/" & Year & " 00:0:01"), _
    '                                                GetCashBalance("8/1/" & Year & " 00:0:01"), _
    '                                                GetCashBalance("9/1/" & Year & " 00:0:01"), _
    '                                                GetCashBalance("10/1/" & Year & " 00:0:01"), _
    '                                                GetCashBalance("11/1/" & Year & " 00:0:01"), _
    '                                                GetCashBalance("12/1/" & Year & " 00:0:01"), _
    '                                                GetCashBalance("1/1/" & (Year + 1) & " 00:0:01"), _
    '                                                0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}
    'das.Tables(0).Rows.Add(rowOpenBalance)


    'Dim cmd As New SqlCommand(StrSel, cnn)
    'Dim Reader As SqlDataReader

    'cnn.Open()
    'Reader = cmd.ExecuteReader
    ''While (Reader.Read)

    ''End While
    'cnn.Close()
End Class